﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MovieRental.Entities;
using MovieRental.SqlRepositoryLibrary.Helpers;

namespace MovieRental.SqlRepositoryLibrary.Converters
{
    public abstract class BaseConverter<TEntity, TKey> : ISqlEntityConverter<TEntity, TKey>
    {
        private SqlCommand _command = new SqlCommand();
       
        protected abstract SqlEntityMapping SetUpMapping();

        protected abstract void SetUpPropertyValues(TEntity entity);

        public BaseConverter()
        {
            Mapping = SetUpMapping();
        }

        public SqlEntityMapping Mapping { private set; get; }

        public void SetProperty(string propertyName, object value)
        {
            var meta = Mapping[propertyName];
            SqlParameter parameter = new SqlParameter('@' + propertyName, meta.Type);
            parameter.Value = value;
            parameter.Direction = ParameterDirection.Input;
            _command.Parameters.Add(parameter);
        }

        public abstract TEntity ConvertFromDataReader(SqlDataReader reader);

        protected object GetValueForProperty(SqlDataReader reader, string propertyName)
        {
            return reader.GetValue(reader.GetOrdinal(Mapping[propertyName].Name));
        }

        public virtual SqlCommand GetInsertCommand(TEntity entity)
        {
            _command = new SqlCommand();
            SetUpPropertyValues(entity);

            StringBuilder sb = new StringBuilder();
            foreach (var column in Mapping.Columns)
                sb.Append(column + ',');
            sb.Remove(sb.Length - 1, 1);
            string columnNames = string.Format("({0})", sb);

            sb.Clear();

            foreach (var property in Mapping.Properties)
                sb.Append('@' + property + ',');
            sb.Remove(sb.Length - 1, 1);
            string parameterNames = string.Format("({0})", sb);

            _command.CommandText = string.Format("INSERT INTO {0}{1} VALUES{2}", Mapping.TableName, columnNames, parameterNames);
            return _command;
        }

        public virtual SqlCommand GetUpdateCommand(TEntity entity)
        {
            _command = new SqlCommand();
            SetUpPropertyValues(entity);

            StringBuilder sb = new StringBuilder();
            foreach (var property in Mapping.Properties.Where(x => x != Mapping.IdPropertyName))
                sb.AppendFormat("{0}={1},", Mapping[property].Name, '@' + property);
            sb.Remove(sb.Length - 1, 1);
            string propertySettings = sb.ToString();

            string id = string.Format("{0}={1}", Mapping.IdColumnName, '@' + Mapping.IdPropertyName);

            _command.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", Mapping.TableName, propertySettings, id);

            return _command;
        }

        public virtual SqlCommand GetDeleteCommand(TKey id)
        {
            _command = new SqlCommand();
            _command.CommandText = string.Format("DELETE FROM {0} WHERE {1}={2}", Mapping.TableName, Mapping.IdColumnName, '@' + Mapping.IdPropertyName);
            _command.Parameters.Add(new SqlParameter('@' + Mapping.IdPropertyName, id));
            return _command;
        }

        public virtual SqlCommand GetReadCommand(TKey id)
        {
            _command = new SqlCommand();
            _command.CommandText = string.Format("SELECT * FROM {0} WHERE {1}={2}", Mapping.TableName, Mapping.IdColumnName, '@' + Mapping.IdPropertyName);
            _command.Parameters.Add(new SqlParameter('@' + Mapping.IdPropertyName, id));
            return _command;
        }

        public virtual SqlCommand GetReadAllCommand()
        {
            _command = new SqlCommand();
            _command.CommandText = string.Format("SELECT * FROM {0}", Mapping.TableName);
            return _command;
        }

        protected SqlCommand ConcatenateCommands(SqlCommand firstCommand, SqlCommand secondCommand)
        {
            SqlCommand result = new SqlCommand();
            result.CommandText = firstCommand.CommandText + "; " + secondCommand.CommandText;
            foreach (var param in firstCommand.Parameters)
                result.Parameters.Add(param);
            foreach (var param in secondCommand.Parameters)
                result.Parameters.Add(param);
            return result;
        }
    }
}
